package com.lzc.springboot.mapper;

import com.lzc.springboot.domain.Expert;
import org.apache.ibatis.annotations.*;

@Mapper
public interface ExpertMapper {

  /**
   * 根据用户查询信息查找专家
   * @param expert
   * @return
   */
  @Select("select * from expert where name like concat('%',#{name},'%') and phone like concat('%',#{phone},'%') and email like concat('%',#{email},'%') and level like concat('%',#{level},'%')")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  Expert[] findExpertBySearch(Expert expert);

  /**
   * 根据专家ID查询专家名称
   * @param eid
   * @return
   */
  @Select("select name from expert where id=#{eid}")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  String findNameById(int eid);

  /**
   * 根据用户ID查询专家名称
   * @param id
   * @return
   */
  @Select("select name from expert where uid=#{id}")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  String findNameByUid(int id);

  /**
   * 根据用户ID查询专家电话
   * @param id
   * @return
   */
  @Select("select phone from expert where uid=#{id}")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  String findPhoneByUid(int id);

  /**
   * 根据用户ID查询专家电子邮件
   * @param eid
   * @return
   */
  @Select("select email from expert where uid=#{id}")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  String findEmailByUid(int eid);

  /**
   * 根据用户ID查询专家证件号
   * @param id
   * @return
   */
  @Select("select license from expert where uid=#{id}")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  String findLicenseByUid(int id);

  /**
   * 根据用户ID查询专家快递地址
   * @param id
   * @return
   */
  @Select("select address from expert where uid=#{id}")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  String findAddressByUid(int id);

  /**
   * 根据用户ID查询专家性别
   * @param id
   * @return
   */
  @Select("select sex from expert where uid=#{id}")
  @Options(useCache = false, flushCache = Options.FlushCachePolicy.TRUE)
  String findSexByUid(int id);

  /**
   * 根据专家ID查询专家
   * @param id
   * @return
   */
  @Select("select * from expert where id=#{id}")
  @Options(useCache = false,flushCache = Options.FlushCachePolicy.TRUE)
  Expert findExpertById(int id);

  /**
   * 根据专家ID删除专家
   * @param id
   */
  @Delete("delete from expert where id=#{id}")
  void delExpertById(int id);

  /**
   * 查询所有专家
   * @return
   */
  @Select("select * from expert")
  @Options(useCache = false,flushCache = Options.FlushCachePolicy.TRUE)
  Expert[] findAllExpert();

  /**
   * 根据专家ID修改专家信息
   * @param expert
   */
  @Update("update expert set name=#{name}, sex=#{sex}, phone=#{phone}, email=#{email}, address=#{address} where id=#{id}")
  void changeExpert(Expert expert);

  /**
   * 查询所有未审核的专家
   * @return
   */
  @Select("select * from users where role='专家' and checked='0'")
  @Options(useCache = false,flushCache = Options.FlushCachePolicy.TRUE)
  @Results({
          @Result(property = "uid", column = "id"),
          @Result(
                  property = "name",
                  column = "id",
                  javaType = String.class,
                  one = @One(select = "com.lzc.springboot.mapper.ExpertMapper.findNameByUid")
          ),
          @Result(
                  property = "sex",
                  column = "id",
                  javaType = String.class,
                  one = @One(select = "com.lzc.springboot.mapper.ExpertMapper.findSexByUid")
          ),
          @Result(
                  property = "phone",
                  column = "id",
                  javaType = String.class,
                  one = @One(select = "com.lzc.springboot.mapper.ExpertMapper.findPhoneByUid")
          ),
          @Result(
                  property = "email",
                  column = "id",
                  javaType = String.class,
                  one = @One(select = "com.lzc.springboot.mapper.ExpertMapper.findEmailByUid")
          ),
          @Result(
                  property = "license",
                  column = "id",
                  javaType = String.class,
                  one = @One(select = "com.lzc.springboot.mapper.ExpertMapper.findLicenseByUid")
          ),
          @Result(
                  property = "address",
                  column = "id",
                  javaType = String.class,
                  one = @One(select = "com.lzc.springboot.mapper.ExpertMapper.findAddressByUid")
          ),
  })
  Expert[] findAllUncheckedExpert();

  /**
   * 根据查询内容查询未审核专家
   * @param expert
   * @return
   */
  @Select("select e.* from users u,expert e where e.uid=u.id and u.checked='0' and e.name like concat('%',#{name},'%') and e.phone like concat('%',#{phone},'%') and e.email like concat('%',#{email},'%')")
  @Options(useCache = false,flushCache = Options.FlushCachePolicy.TRUE)
  Expert[] findUncheckedBySearch(Expert expert);

  /**
   * 根据用户ID查询专家
   * @param uid
   * @return
   */
  @Select("select * from expert where uid=#{uid}")
  @Options(useCache = false,flushCache = Options.FlushCachePolicy.TRUE)
  Expert findExpertByUid(int uid);

  /**
   * 根据专家输入，修改自身的信息
   * @param expert
   */
  @Update("update expert set name=#{name}, sex=#{sex}, phone=#{phone}, email=#{email}, address=#{address},info=#{info} where id=#{id}")
  void changeExpertByExpert(Expert expert);

  /**
   * 添加专家
   * @param expert
   */
  @Insert("insert into expert values(null,#{uid},#{name},#{sex},#{phone},#{email},#{level},#{license},#{info},#{question_count},#{tol_rate},#{address})")
  void addExpert(Expert expert);
}
